package com.scs.application.core.export.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.scs.application.core.export.entity.ExportParams;
import com.scs.application.core.utils.*;
import com.scs.application.modules.base.dto.ExportHeadDTO;
import com.scs.application.modules.report.entity.Config;
import com.scs.application.modules.sys.adapter.DictAdapter;
import com.scs.application.modules.sys.adapter.FlowAdapter;
import com.scs.application.modules.sys.entity.DictData;
import com.scs.application.modules.sys.entity.DictType;
import com.scs.application.modules.sys.entity.FlowNode;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class ExportService {

    @Value("${res.real-path}")
    private String resPath;

    @Autowired
    @Lazy
    private DictAdapter dictAdapter;

    @Autowired
    @Lazy
    private FlowAdapter flowAdapter;

    /**
     * 导出数据
     */
    public String export(List exportData, ExportParams exportParams) throws Exception {
        List<ExportHeadDTO> exportHeadDTOList = new ArrayList<>();
        //表头列
        List<Object> fields = JSONArray.parseArray(exportParams.getFields());
        fields.forEach(field -> {
            ExportHeadDTO exportHeadDTO = new ExportHeadDTO();
            JSONObject jsonObject = (JSONObject) field;
            exportHeadDTO.setName(jsonObject.get("field").toString())
                    .setText(jsonObject.getString("title"))
                    .setDict(jsonObject.get("dict")+"")
                    .setDatePattern(jsonObject.get("datePattern")+"")
                    .setFlowId(jsonObject.get("flowId")+"");
            if (jsonObject.containsKey("isRequired")) {
                exportHeadDTO.setRequired(jsonObject.getBooleanValue("isRequired"));
            } else {
                exportHeadDTO.setRequired(false);
            }
            exportHeadDTOList.add(exportHeadDTO);
        });

        //表头列 第二行
        List<ExportHeadDTO> exportHeadDTOList2 = new ArrayList<>();
        List<Object> fields2 = JSONArray.parseArray(exportParams.getFields2());
        if (fields2 != null && fields2.size() > 0){
            fields2.forEach(field -> {
                ExportHeadDTO exportHeadDTO = new ExportHeadDTO();
                JSONObject jsonObject = (JSONObject) field;
                exportHeadDTO.setName(jsonObject.get("field").toString())
                        .setText(jsonObject.get("title").toString())
                        .setDict(jsonObject.get("dict")+"")
                        .setDatePattern(jsonObject.get("datePattern")+"")
                        .setFlowId(jsonObject.get("flowId")+"");
                exportHeadDTOList2.add(exportHeadDTO);
            });
        }

//        String sheetName = exportParams.getSheetName();
        String sheetName = "sheet1";
        String fileName = exportParams.getFilename();
        if (StringUtils.isEmpty(fileName)) {
            fileName = DateUtils.format(new Date(), "yyyyMMddHHmmss");
        }
        String fileNameAll = fileName + ".xls";
        String downloadPath = "download/" + fileNameAll;
        String tmpFilePath = resPath + downloadPath;
        FileUtils.mkdirs(resPath + "download");
        File tmpFile = new File(tmpFilePath);
        log.debug("tmpFilePath={}", tmpFilePath);

        HSSFWorkbook workbook = new HSSFWorkbook();
        //工作表
        HSSFSheet sheet = workbook.createSheet(sheetName);

        int rowNextNum = 0;
        HSSFRow row;
        HSSFCell cell;
        HSSFFont font;
        HSSFCellStyle cellStyle;
        HSSFRichTextString richTextString;
        //表头列
        if (exportParams.getIsHeader()) {
            row = sheet.createRow(rowNextNum);
            for (int i = 0; i < exportHeadDTOList.size(); i++) {
                ExportHeadDTO exportHeadDTO = exportHeadDTOList.get(i);
                cell = row.createCell(i);
                exportHeadDTO.setText(exportHeadDTO.getText().replaceAll("#","\r\n"));
                richTextString = new HSSFRichTextString(UtilCommon.getStr(exportHeadDTO.getText(), ""));
                cell.setCellValue(richTextString);

                font = workbook.createFont();
                //字体大小
                font.setFontHeightInPoints((short) 11);
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //字体加粗

                if (exportHeadDTO.isRequired()) {
                    font.setColor(HSSFFont.COLOR_RED);
                }

                cellStyle = workbook.createCellStyle();
                //水平居中
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                cellStyle.setFont(font);
                cellStyle.setWrapText(true);

                cell.setCellStyle(cellStyle);

                if (exportHeadDTO.getText().indexOf("\n") != -1) { //包含换行的
                    String[] headArr = exportHeadDTO.getText().split("\n");
                    String headMax = "";
                    for(String head : headArr){
                        if (head.getBytes().length > headMax.getBytes().length) headMax =head;

                    }
                    //cell宽度
                    sheet.setColumnWidth(i, (int) (headMax.getBytes().length * 1.2d * 256 > 12 * 256 ? headMax.getBytes().length * 1.2d * 256 : 12 * 256));
                }else {
                    //cell宽度
                    sheet.setColumnWidth(i, (int) (UtilCommon.getStr(exportHeadDTO.getText(), "").getBytes().length * 1.2d * 256 > 12 * 256 ? UtilCommon.getStr(exportHeadDTO.getText(), "").getBytes().length * 1.2d * 256 : 12 * 256));
                }
            }
            rowNextNum++;

            //第二行表头
            if (exportHeadDTOList2 != null && exportHeadDTOList2.size() > 0) {
                row = sheet.createRow(rowNextNum);
                for (int i = 0; i < exportHeadDTOList2.size(); i++) {
                    ExportHeadDTO exportHeadDTO = exportHeadDTOList2.get(i);
                    cell = row.createCell(i);
                    richTextString = new HSSFRichTextString(UtilCommon.getStr(exportHeadDTO.getText(), ""));
                    cell.setCellValue(richTextString);

                    font = workbook.createFont();
                    //字体大小
                    font.setFontHeightInPoints((short) 9);
                    cellStyle = workbook.createCellStyle();
                    //水平居中
                    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    cellStyle.setFont(font);
                    cellStyle.setWrapText(true);
                    cell.setCellStyle(cellStyle);

                    if (exportHeadDTO.getText().indexOf("\n") != -1) { //包含换行的
                        String[] headArr = exportHeadDTO.getText().split("\n");
                        String headMax = "";
                        for(String head : headArr){
                            if (head.getBytes().length > headMax.getBytes().length) headMax =head;

                        }
                        //cell宽度
                        sheet.setColumnWidth(i, (int) (headMax.getBytes().length * 1.2d * 256 > 12 * 256 ? headMax.getBytes().length * 1.2d * 256 : 12 * 256));
                    }else {
                        //cell宽度
                        sheet.setColumnWidth(i, (int) (UtilCommon.getStr(exportHeadDTO.getText(), "").getBytes().length * 1.2d * 256 > 12 * 256 ? UtilCommon.getStr(exportHeadDTO.getText(), "").getBytes().length * 1.2d * 256 : 12 * 256));
                    }

                    sheet.setColumnWidth(i, (int) (sheet.getColumnWidth(i)*0.6));
                }
                rowNextNum++;
            }
        }

        try {
            List<DictData> dictDataList = null;
            List<FlowNode> flowNodeList = null;
            //数据区域
            for (Object exportDataRow : exportData) {
                row = sheet.createRow(rowNextNum);
                for (int i = 0; i < exportHeadDTOList.size(); i++) {
                    ExportHeadDTO exportHeadDTO = exportHeadDTOList.get(i);
                    String fieldValue = getFieldValueByName(exportHeadDTO.getName(), exportDataRow);
                    if (StringUtils.isNotBlank(fieldValue)) {
                        if (StringUtils.isNotBlank(exportHeadDTO.getDatePattern()) || exportHeadDTO.getName().toLowerCase().indexOf("time") != -1) { //日期类型
                            if (StringUtils.isBlank(exportHeadDTO.getDatePattern())) exportHeadDTO.setDatePattern("yyyy-MM-dd HH:mm:ss");

                            String year = fieldValue.substring(0, 4);
                            try {
                                if (fieldValue.lastIndexOf(".0") != -1) {
                                    //包含毫秒的先替换，
                                    //2012-12-12 12:12:12.0
                                    fieldValue=fieldValue.substring(0,fieldValue.indexOf("."));
                                    fieldValue = DateUtils.formatToAnotherPattern(fieldValue,exportHeadDTO.getDatePattern(),exportHeadDTO.getDatePattern());
                                }  else if(UtilNum.isPureDigital(year)&&fieldValue.lastIndexOf("T") != -1){
                                    fieldValue = fieldValue.replaceAll("T", " ");
                                } else if(fieldValue.contains("CST")){
                                    fieldValue = fieldValue.split(" ")[0]+" "+fieldValue.split(" ")[1]+" "+fieldValue.split(" ")[2]+" "+fieldValue.split(" ")[5]+" "+fieldValue.split(" ")[3]+" GMT+0800";
                                    fieldValue = DateUtils.format(new Date(fieldValue), exportHeadDTO.getDatePattern());
                                }
                                else {
                                    fieldValue = DateUtils.format(new Date(fieldValue), exportHeadDTO.getDatePattern());
                                }
                            }catch (Exception e) {
                                log.error("export，日期转换失败:" ,e);
                            }
                        } else if (StringUtils.isNotBlank(exportHeadDTO.getFlowId())) { //流程类型
                            final String fieldFlowValue = fieldValue;
                            if (flowNodeList == null) {
                                flowNodeList = flowAdapter.getFlowNodeService().list();
                            }
                            Optional<FlowNode> optionalFlowNode = flowNodeList.stream().filter(flow -> flow.getFlowId().equals(exportHeadDTO.getFlowId()) && flow.getValue().equals(fieldFlowValue)).findFirst();
                            if (optionalFlowNode.isPresent()) {
                                fieldValue = optionalFlowNode.get().getName();
                            }
                        } else if (StringUtils.isNotBlank(exportHeadDTO.getDict())) { //字典类型
                            final String fieldIntValue = StringUtils.parseBoolToInt(fieldValue);
                            if (dictDataList == null) {
                                dictDataList = dictAdapter.getDictDataService().list();
                            }
                            Optional<DictData> optionalDictData = dictDataList.stream().filter(dict -> dict.getDictTypeCode().equals(exportHeadDTO.getDict()) && dict.getValue().equals(fieldIntValue)).findFirst();
                            if (optionalDictData.isPresent()) {
                                fieldValue = optionalDictData.get().getLabel();
                            }
                        }
                    }
                    richTextString = new HSSFRichTextString(fieldValue);
                    row.createCell(i).setCellValue(richTextString);
                }
                rowNextNum++;
            }


            OutputStream os = new FileOutputStream(tmpFile);
            workbook.write(os);
            return downloadPath;
        } catch (IOException e) {
            log.error("操作失败，原因:" + e.getMessage());
            throw e;
        } catch (Exception ex) {
            log.error("操作失败，原因:" + ex.getMessage());
            throw ex;
        }
    }

    private String getFieldValueByName(String fieldName, Object o) throws Exception {
        Object value;
        if (o instanceof HashMap) { //是hashMap
            value = ((HashMap) o).get(fieldName);
        } else { //是实体类
            Method method = o.getClass().getMethod("get" + StringUtils.capitalize(fieldName), new Class[]{});
            value = method.invoke(o, new Object[]{});
        }
        return value == null ? "" : value.toString();
    }


    public void  exportReport(
            Config config,
            HSSFWorkbook workbook,
            String sheetName,
            List<ExportHeadDTO> exportHeadDTOListAll,
            List<Map<String, Object>> exporSumDataList,
            List<ExportHeadDTO> exportHeadDTOList1,
            List<ExportHeadDTO> exportHeadDTOList2,
            List<Map<String, Object>> exporDataList,
            JSONArray exportFooterSumArr,
            JSONArray exportFooterSignArr
    ){
        HSSFSheet sheet = workbook.createSheet(sheetName);

        int rowNextNum = 0, colMax = exportHeadDTOListAll.size();
        HSSFRow row;
        HSSFCell cell;
        HSSFFont font;
        HSSFCellStyle cellStyle;
        HSSFRichTextString richTextString = null;
        String sheet1Title = config.getName();
        String sheet1SubTitle = config.getSubtitle();


        HSSFCellStyle cellStyleTitle;
        font = workbook.createFont();
        //字体大小
        font.setFontHeightInPoints((short) 16);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //字体加粗
        cellStyleTitle = workbook.createCellStyle();
        //水平居中
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleTitle.setFont(font);


        HSSFCellStyle cellStyleValue;
        font = workbook.createFont();
        //字体大小
        font.setFontHeightInPoints((short) 11);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //字体加粗
        cellStyleValue = workbook.createCellStyle();
        //水平居中
        cellStyleValue.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleValue.setFont(font);
        //标题列
        if (!UtilCommon.strIsNull(sheet1Title)) {
            row = sheet.createRow(rowNextNum);
            cell = row.createCell(0);
            richTextString = new HSSFRichTextString(sheet1Title);
            cell.setCellValue(richTextString);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colMax));// 开始行，结束行，开始列，结束列。


            cell.setCellStyle(cellStyleTitle);
            rowNextNum++;
        }

        //子标题列
        if (!UtilCommon.strIsNull(sheet1SubTitle)) {
            row = sheet.createRow(rowNextNum);
            cell = row.createCell(0);
            richTextString = new HSSFRichTextString(sheet1SubTitle);
            cell.setCellValue(richTextString);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, colMax));// 开始行，结束行，开始列，结束列。

            cell.setCellStyle(cellStyleTitle);
            rowNextNum++;
        }

        //查询条件

        //顶部合计区域
        if( !(config.getFlagTopSum() != null && config.getFlagTopSum()) &&  exporSumDataList !=null && exporSumDataList.size() > 0) {
            row = sheet.createRow(rowNextNum);

            richTextString = new HSSFRichTextString("合计");
            cell = row.createCell(0);
            cell.setCellValue(richTextString);

            cell.setCellStyle(cellStyleTitle);
            sheet.addMergedRegion(new CellRangeAddress(rowNextNum, rowNextNum, 0, 1));// 开始行，结束行，开始列，结束列。


            int cellIndex =2;
            for (int i=0;i<exporSumDataList.size();i++) {
                Map<String, Object> sumColumn = exporSumDataList.get(i);
                richTextString = new HSSFRichTextString(UtilFieldMap.show((String)sumColumn.get("field"),(String)sumColumn.get("label"))+":"+sumColumn.getOrDefault("sum","0"));
                cell = row.createCell(cellIndex);
                cell.setCellValue(richTextString);

                cell.setCellStyle(cellStyleTitle);
                sheet.addMergedRegion(new CellRangeAddress(rowNextNum, rowNextNum, cellIndex, cellIndex+2));// 开始行，结束行，开始列，结束列。
                cellIndex+=3;
            }
            rowNextNum++;
        }


        //表头列-一级
        ExportHeadDTO exportHeadDTO = null;
        if (exportHeadDTOList1 != null && exportHeadDTOList1.size() > 0){
            row = sheet.createRow(rowNextNum);
            int cellIndex = 0;
            for (int i = 0; i < exportHeadDTOList1.size(); i++) {
                exportHeadDTO = exportHeadDTOList1.get(i);
                cell = row.createCell(cellIndex);
                richTextString = new HSSFRichTextString(UtilCommon.getStr(UtilFieldMap.show(exportHeadDTO.getName(), exportHeadDTO.getText()), ""));
                cell.setCellValue(richTextString);

                cell.setCellStyle(cellStyleTitle);

                //改行跨列
                if (exportHeadDTO.getColspan() > 1){

                    sheet.addMergedRegion(new CellRangeAddress(rowNextNum, rowNextNum, cellIndex, cellIndex+(exportHeadDTO.getColspan()-1)));
                    cellIndex+=exportHeadDTO.getColspan();
                }else {
                    cellIndex++;
                }

                //cell宽度
                sheet.setColumnWidth(i, (int) (UtilCommon.getStr(UtilFieldMap.show(exportHeadDTO.getName(), exportHeadDTO.getText()), "").getBytes().length * 1.2d * 256 > 12 * 256 ? UtilCommon.getStr(exportHeadDTO.getText(), "").getBytes().length * 1.2d * 256 : 12 * 256));
            }
            rowNextNum++;
        }


        //表头列-二级
        //二级列第一列左边空列数量
        final int[] leftEmptyColNum = {0};
        if (exportHeadDTOList2 != null && exportHeadDTOList2.size() > 0){
            row = sheet.createRow(rowNextNum);
            //加个空单元格，占位序号列
            row.createCell(0);
            int cellIndex = leftEmptyColNum[0];
            for (int i = 0; i < exportHeadDTOList2.size(); i++) {
                exportHeadDTO = exportHeadDTOList2.get(i);
                cell = row.createCell(cellIndex);
                richTextString = new HSSFRichTextString(UtilCommon.getStr(UtilFieldMap.show(exportHeadDTO.getName(), exportHeadDTO.getText()), ""));
                cell.setCellValue(richTextString);

                cell.setCellStyle(cellStyleTitle);
                cellIndex++;
                //cell宽度
                sheet.setColumnWidth(i, (int) (UtilCommon.getStr(UtilFieldMap.show(exportHeadDTO.getName(), exportHeadDTO.getText()), "").getBytes().length * 1.2d * 256 > 12 * 256 ? UtilCommon.getStr(exportHeadDTO.getText(), "").getBytes().length * 1.2d * 256 : 12 * 256));
            }
            rowNextNum++;
        }


        //数据字典列表
        List<ExportHeadDTO> selectDictList = exportHeadDTOListAll.stream().filter(ob -> "select".equals(ob.getQueryType())).collect(Collectors.toList());
        List<DictType> dictTypeList = null;
        List<DictData> dictDataList = null;
        if (selectDictList != null && selectDictList.size() > 0) {
            List<String> codeList = selectDictList.stream().map(ExportHeadDTO::getDict).collect(Collectors.toList());
            dictTypeList = dictAdapter.getDictTypeService().list(Wrappers.<DictType>query().in("code", codeList));
            dictDataList = dictAdapter.getDictDataService().list(Wrappers.<DictData>query().in("dict_type_code", codeList));
        }

        //数据区域
        CellStyle cellStyleNum = workbook.createCellStyle();
        // 右对齐
        cellStyleNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        for (Map<String, Object> exportDataRow : exporDataList) {
            row = sheet.createRow(rowNextNum);
            for (int i = 0; i < exportHeadDTOListAll.size(); i++) {
                exportHeadDTO = exportHeadDTOListAll.get(i);
                String value = exportDataRow.get(exportHeadDTO.getName()) + "";
                //数据字典
                if (StringUtils.isNotBlank(exportHeadDTO.getQueryType()) && "select".equals(exportHeadDTO.getQueryType())) {
                    ExportHeadDTO finalExportHeadDTO = exportHeadDTO;
                    String finalValue = value;
                    List<DictData> dictDataListFind =
                            dictDataList.stream()
                                    .filter(dictData -> dictData.getDictTypeCode().equals(finalExportHeadDTO.getDict()) && dictData.getValue().equals(finalValue)).collect(Collectors.toList());

                    if (dictDataListFind == null || dictDataListFind.size() < 1)
                        dictDataListFind = dictDataList.stream()
                                .filter(dictData -> dictData.getDictTypeCode().equals(finalExportHeadDTO.getDict()) && dictData.getLabel().equals(finalValue)).collect(Collectors.toList());

                    if (dictDataListFind != null && dictDataListFind.size() == 1)
                        value = dictDataListFind.get(0).getLabel();
                }
                String nameLower = exportHeadDTO.getName().toLowerCase();
                boolean isNum =false;
                try {
                    if (UtilNum.calcDecimalNum(value) > 2) {
                        isNum =true;
                        value = UtilNum.toDecimal(UtilNum.getDouble(value), 2);
                    }
                } catch (Exception e) {
                    //log.error("calcDecimalNum.error",e);
//                    log.error("calcDecimalNum.error value = {}",value);
                }
                if (isNum) {
                    HSSFCell cellNew = row.createCell(i);

                    DataFormat df = workbook.createDataFormat();
                    cellStyleNum.setDataFormat(df.getFormat(UtilNum.getNumCellFormat(value)));//保留4位小数点
                    richTextString = new HSSFRichTextString(value);
                    if (UtilNum.isNumber(value)) {
                        cellNew.setCellValue(Double.parseDouble(value));
                    }else {
                        cellNew.setCellValue(richTextString);
                    }
                    cellNew.setCellStyle(cellStyleNum);
                } else {
                    richTextString = new HSSFRichTextString(UtilCommon.getStr(value, ""));
                    row.createCell(i).setCellValue(richTextString);
                }
            }
            rowNextNum++;
        }

        //底部合计区域
        if (!(config.getFlagBottomSum() != null && config.getFlagBottomSum()) && exportFooterSumArr != null) {
            row = sheet.createRow(rowNextNum);
            int cellIndex = 0;
            for (int i = 0; i < exportFooterSumArr.size(); i++) {
                String sumColumn = UtilCommon.getStr(exportFooterSumArr.get(i) + "", "");
                richTextString = new HSSFRichTextString(sumColumn);
                cell = row.createCell(cellIndex);
                cell.setCellValue(richTextString);

                cell.setCellStyle(cellStyleValue);
                cellIndex++;
            }
            rowNextNum++;
        }

        //底部签名栏
        if (exportFooterSignArr != null) {
            row = sheet.createRow(rowNextNum);
            int cellIndex = 0;
            for (int i = 0; i < exportFooterSignArr.size(); i++) {
                JSONObject sumColumn = exportFooterSignArr.getJSONObject(i);
                richTextString = new HSSFRichTextString(sumColumn.getString("text") + ":" + UtilCommon.getStr(sumColumn.getString("value"), ""));
                cell = row.createCell(cellIndex);
                cell.setCellValue(richTextString);
                cell.setCellStyle(cellStyleTitle);
                sheet.addMergedRegion(new CellRangeAddress(rowNextNum, rowNextNum, cellIndex, cellIndex + 1));// 开始行，结束行，开始列，结束列。
                cellIndex += 2;
            }
            rowNextNum++;
        }
    }
}
